﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace lINQtoSQL
{
    class Program
    {
        static void Main(string[] args)
        {

            //Write a method that finds all customers who have orders made in 1997 and 
            //shipped to Canada.

            //DataClassesNorthwindDataContext context = new DataClassesNorthwindDataContext();
            //using (context)
            //{
            //    var OrdersIn97Canada =
            //        (from c in context.Customers
            //        join
            //            o in context.Orders on c.CustomerID equals o.CustomerID
            //        where o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada"
            //        select c).Distinct() ;
            //    foreach (var item in OrdersIn97Canada)
            //    {
            //        Console.WriteLine(item.CompanyName);
            //    }


            //}


            //Implement previous by using native SQL query and executing it through the 
            //DataContext.

   //              DataClassesNorthwindDataContext context = new DataClassesNorthwindDataContext();
   //              using (context)
   //              {
   //                  var customers = context.ExecuteQuery<string>(
   //"select distinct companyName from Customers c join Orders o on c.CustomerID=o.CustomerID where ShipCountry='Canada' and Year(OrderDate)=1997");

   //                  foreach (var item in customers)
   //                  {
   //                      Console.WriteLine(item);
   //                  }


   //              }


            //Write a method that finds all the sales by specified region and period 
            //(start / end dates).

            //AllSales(null, new DateTime(1997,01,01), new DateTime(1997,12,31));

            //Create a database called NorthwindTwin with the same structure as Northwind 
            //using the features from DataContext. Find for the API for schema generation 
            //in MSDN or in Google.

   //         DataClassesNorthwindDataContext db = new DataClassesNorthwindDataContext(
   //@"Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\NorthWind1.mdf;Database=NorthWindCopy11; Trusted_Connection=yes");
   //        db.CreateDatabase();



        }

        public static void AllSales(string region,DateTime startDate,DateTime endDate)
    {
        DataClassesNorthwindDataContext context = new DataClassesNorthwindDataContext();
        using (context)
        {
            var orders =
                from o in context.Orders
                where o.ShipRegion==region && o.OrderDate > startDate && o.ShippedDate < endDate
                 select o;
            foreach (var item in orders)
            {
                Console.WriteLine(item.OrderID+" "+item.ShipAddress);
            }


        }

    }
    }
}
